/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 * @data:2017年10月16日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wttech.dzzf.process.model.CertKey;

/**
 * 不同手机号记录Dao
 * @date 2017年10月16日
 * @author MaShibo
 */
@Repository
public class DifferentPhoneInfosDao extends BaseDataTemplate{
    
    @Resource(name = "dzzfJdbcTemplate")
    private JdbcTemplate dzzfJdbcTemplate;
    
    @Resource(name = "jdbcTemplate")
    private JdbcTemplate testJdbcTemplate;
    
    public static final String QUERY_CERTINFO_SQL = 
            "SELECT M.*, ROWNUM AS RM " +
                    "  FROM (SELECT A.CERTIFINO, A.CERTIFITYPE " + 
                    "          FROM AC_FUNDACCOUNT A " + 
                    "         WHERE A.ACCOUNTSTATUS = 1 " + 
                    "         GROUP BY A.CERTIFINO, A.CERTIFITYPE " + 
                    "        HAVING COUNT(1) > 1) M";


    
    public Integer queryRepeatCertInfoNum(){
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(1) FROM ( ");
        sql.append(QUERY_CERTINFO_SQL);
        sql.append(" ) ");
        return testJdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }
    
    public List<CertKey> queryAllRepeatCertInfos(int startRow, int endRow) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ( ");
        sql.append(QUERY_CERTINFO_SQL);
        sql.append(" ) ");
        sql.append(" WHERE RM >= ? ");
        sql.append(" AND RM <= ? ");
        return testJdbcTemplate.query(sql.toString(), new Object[] { startRow, endRow },
                BeanPropertyRowMapper.newInstance(CertKey.class));
    }
    
    @SuppressWarnings("unchecked")
    public List<FundAccount> queryFundAccountInfosByCert(String certifiNo, Integer certifiType) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT * ");
        sql.append("   FROM AC_FUNDACCOUNT A ");
        sql.append("  WHERE A.ACCOUNTSTATUS = 1 ");
        sql.append("    AND A.CERTIFINO = ? ");
        sql.append("    AND A.CERTIFITYPE = ? ");
        sql.append("  ORDER BY A.OPTON DESC ");
        return (List<FundAccount>) testJdbcTemplate.query(sql.toString(), new Object[] { certifiNo, certifiType },
                new ListResultSetExtractor(new FundAccount()));
    }
}
